package com.sxfq.loansupermarket.backsystem.modular.refunds.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.constant.tips.Tip;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.CheckoutMethodInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.CaseInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RefundInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.ReservationsArchingInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.SearchRechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.IRechargeService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.PaymentInfo;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.validation.Valid;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * 预约客户信息
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/recharge")
public class RechargeController extends BaseController {

    @Autowired
    IRechargeService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private SqlMapper sqlMapper;


    private String PREFIX = "/arrival/customer/";

    /**
     * 跳转到预约客户管理页面
     */
    @RequestMapping(value = "")
    public String recharge(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "/customer_recharge.html";
    }

    private double subtract(double v1, double v2) {
        BigDecimal b1 = new BigDecimal(Double.toString(v1));
        BigDecimal b2 = new BigDecimal(Double.toString(v2));
        return b1.subtract(b2).doubleValue();
    }

    @RequestMapping(value = "/getCheckoutMethod/{croi}")
    public String getCheckoutMethod(@PathVariable String croi, Model model) {
        String getTime = croi.split(",")[0];
        String id = croi.split(",")[1];
        String payorrefund = croi.split(",")[2];
        String customerId = croi.split(",")[3];
        String reservationsId = croi.split(",")[4];
        String id2 = croi.split(",")[5];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from checkout_method_info where id='" + id + "';";
        String tableName1 = tableHead + "deal_info";
        String selectsql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.deal_status!='未成交' and  t1.operation_time='" + getTime + "' and t1.reservations_id='" + reservationsId + "'";
        List<PayServiceInfo> openBillList = sqlMapper.selectList(selectsql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        for (PayServiceInfo info : openBillList) {
            if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                totleFoldingPay += info.getFoldingPay();
                totleServiceActualPay += info.getServiceActualPay();
                info.setTotleReceivable(totleReceivable);
                info.setTotleFoldingPay(totleFoldingPay);
                info.setTotleServiceActualPay(totleServiceActualPay);
            }
        }
        String selectsql1 = "select * from " + tableName1 + " where id='" + id2 + "'";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(selectsql1, RechargeInfo.class);
        CheckoutMethodInfo checkoutMethodInfo = sqlMapper.selectOne(sql, CheckoutMethodInfo.class);
        String sql2 = "select sum(a.actual_account_balance) as actualAccountBalance from temporary_recharge_info a where id2='" + rechargeInfo.getId2() + "' and recharge_people='" + ShiroKit.getUser().getAccount() + "'";
        RechargeInfo rechargeInfo1 = sqlMapper.selectOne(sql2, RechargeInfo.class);
        Double money1 = 0.00;
        if (rechargeInfo1 != null) {
            money1 = rechargeInfo1.getActualAccountBalance();
        }
        String sql3 = "select a.service_actual_pay as serviceActualPay from " + tableName1 + " a where id2='" + rechargeInfo.getId2() + "' and get_time='" + getTime + "'";
        RechargeInfo rechargeInfo2 = sqlMapper.selectOne(sql3, RechargeInfo.class);
        Double money2 = rechargeInfo2.getServiceActualPay();
        Double money = subtract(money2, money1);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("totleFoldingPay", totleFoldingPay);
        model.addAttribute("totleServiceActualPay", totleServiceActualPay);
        model.addAttribute("checkoutMethodInfo", checkoutMethodInfo);
        model.addAttribute("serviceActualPay", money);
        model.addAttribute("getTime", getTime);
        model.addAttribute("id2", rechargeInfo.getId2());
        model.addAttribute("payorrefund", payorrefund);
        return PREFIX + "/getCheckoutMethod.html";
    }

    @RequestMapping(value = "/getCheckoutMethodd/{croi}")
    public String getCheckoutMethodd(@PathVariable String croi, Model model) {
        String serviceActualPay = croi.split(",")[0];
        String customerId = croi.split(",")[1];
        String reservationsId = croi.split(",")[2];
        String operationTime = croi.split(",")[3];
        String id = croi.split(",")[4];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from checkout_method_info where id='" + id + "';";
        String tableName1 = tableHead + "deal_info";
        String selectsql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.deal_status!='未成交' and  t1.operation_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "'";
        List<PayServiceInfo> openBillList = sqlMapper.selectList(selectsql, PayServiceInfo.class);
        Double totleServiceActualPay = 0.00;
        String remarks = "";
        for (PayServiceInfo info : openBillList) {
            if (info != null) {
                totleServiceActualPay += info.getServiceActualPay();
                info.setTotleServiceActualPay(totleServiceActualPay);
                remarks = info.getRemarks();
            }
        }
        CheckoutMethodInfo checkoutMethodInfo = sqlMapper.selectOne(sql, CheckoutMethodInfo.class);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("totleServiceActualPay", totleServiceActualPay);
        model.addAttribute("remarks", remarks);
        model.addAttribute("checkoutMethodInfo", checkoutMethodInfo);
        model.addAttribute("serviceActualPay", serviceActualPay);
        return PREFIX + "/getCheckoutMethodd.html";
    }

    @RequestMapping(value = "/addSpecialSupplement", method = RequestMethod.POST)
    @ResponseBody
    public int addSpecialSupplement(String customerId, String reservationsId, Double supplementMoney, String remarks, HttpServletRequest request) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        String id = CommUtils.getUUID();
        String time = DateTimeKit.now();
        String insertSql = "insert into " + tableName1 + " " +
                "(id,customer_id,reservations_id,service_actual_pay,remarks,deal_status,operation_time,outpatient_name ) VALUES" +
                "('" + id + "','" + customerId + "','" + reservationsId + "'," + supplementMoney + ",'" + remarks + "','特殊补欠款','" + time + "','" + ShiroKit.getSessionAttr("outpatientName") + "')";
        return sqlMapper.insert(insertSql);
    }

    @RequestMapping(value = "/to_specialSupplement/{rc}")
    public String to_specialSupplement(@PathVariable String rc, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String reservationsId = rc.split(",")[0];
        String customerId = rc.split(",")[1];
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        return PREFIX + "special_supplement.html";
    }

    @RequestMapping(value = "/getCheckoutMethods/{croi}")
    public String getCheckoutMethods(@PathVariable String croi, Model model) {
        String serviceActualPay = croi.split(",")[0];
        String customerId = croi.split(",")[1];
        String reservationsId = croi.split(",")[2];
        String operationTime = croi.split(",")[3];
        String id = croi.split(",")[4];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from checkout_method_info where id='" + id + "';";
        String tableName1 = tableHead + "deal_info";
        String selectsql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService,t1.arrears as arrears," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.get_time as getTime," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.deal_status!='未成交' and  t1.operation_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "' and t1.get_time=t1.operation_time";
        List<PayServiceInfo> openBillList = sqlMapper.selectList(selectsql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;
        String getTime = "";
        for (PayServiceInfo info : openBillList) {
            if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
            }
            totleFoldingPay += info.getFoldingPay();
            totleServiceActualPay += info.getServiceActualPay();
            accountArrears += info.getArrears();
            info.setTotleReceivable(totleReceivable);
            info.setTotleFoldingPay(totleFoldingPay);
            info.setTotleServiceActualPay(totleServiceActualPay);
            getTime = info.getGetTime();
        }
        CheckoutMethodInfo checkoutMethodInfo = sqlMapper.selectOne(sql, CheckoutMethodInfo.class);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("getTime", getTime);
        model.addAttribute("totleFoldingPay", totleFoldingPay);
        model.addAttribute("accountArrears", accountArrears);
        model.addAttribute("totleServiceActualPay", totleServiceActualPay);
        model.addAttribute("checkoutMethodInfo", checkoutMethodInfo);
        model.addAttribute("serviceActualPay", serviceActualPay);
        return PREFIX + "/getCheckoutMethods.html";
    }


    /**
     *
     */
    @RequestMapping("/rechargeList")
    @ResponseBody
    public Object rechargeList(SearchRechargeInfo info, HttpServletRequest request, Model model) {
        Page<RechargeInfo> page = new PageFactory<RechargeInfo>().defaultPage();
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<RechargeInfo> list = service.selectPageListRechargeInfo(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = service.selectPageListRechargeInfo(page, info);
            }
            page.setTotal(service.getQueryPagesCount(page, info));
            page.setRecords(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 添加到店客户
     */
    @RequestMapping(value = "/addRecharge")
    @ResponseBody
    public int addRecharge(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String id = CommUtils.getUUID();
        Double giveAccountBalance = 0.00;
        if (info.getGiveAccountBalance() == null) {
            giveAccountBalance = 0.00;
        } else {
            giveAccountBalance = info.getGiveAccountBalance();
        }
        String sql = "INSERT INTO " + tableName + " (`id`,`customer_id`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`give_account_balance`, `record_time`) VALUES" +
                "('" + id + "','" + info.getId() + "'," + 2 + ",'" + info.getReservationsId() + "','" + ShiroKit.getUser().getAccount() + "" +
                "','" + info.getActualAccountBalance() + "'," + giveAccountBalance + ",'" + DateTimeKit.now() + "');";
        return sqlMapper.insert(sql);
    }


    /**
     * 删除
     */
    @RequestMapping(value = "/delete/{id}")
    @ResponseBody
    public Tip delete(@PathVariable String id) throws Exception {
//        service.delete(id);
        service.deletes(id);
        return SUCCESS_TIP;
    }


    @RequestMapping(value = "/to_recharge/{customerId}")
    public String to_recharge(@PathVariable String customerId, Model model) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        CustomerInfo customerInfo = customerService.selectOneById(customerId);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", customerInfo.getReservationsId());
        return PREFIX + "recharge.html";
    }

    @RequestMapping(value = "/to_choiceCheckoutMethod/{rco}")
    public String to_choiceCheckoutMethod(@PathVariable String rco, Model model) {
        String id = rco.split(",")[0];
        String payorrefund = rco.split(",")[1];
        String reservationsId = rco.split(",")[2];
        String customerId = rco.split(",")[3];
        String operationTime = rco.split(",")[4];
        String getTime = rco.split(",")[5];
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName = tableHead + "deal_info";
        String sql = "select * from checkout_method_info";
        List<CheckoutMethodInfo> checkoutMethodInfo = sqlMapper.selectList(sql, CheckoutMethodInfo.class);
        String selectsql = "select * from " + tableName + " where id='" + id + "'";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(selectsql, RechargeInfo.class);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("checkoutMethodInfo", checkoutMethodInfo);
        model.addAttribute("rechargeInfo", rechargeInfo);

        String selectsql2 = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause from " + tableName + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.deal_status!='未成交' and  t1.operation_time='" + getTime + "' and t1.reservations_id='" + reservationsId + "'";
        List<PayServiceInfo> openBillList = sqlMapper.selectList(selectsql2, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        for (PayServiceInfo info : openBillList) {
            if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                totleFoldingPay += info.getFoldingPay();
                totleServiceActualPay += info.getServiceActualPay();
                info.setTotleReceivable(totleReceivable);
                info.setTotleFoldingPay(totleFoldingPay);
                info.setTotleServiceActualPay(totleServiceActualPay);
            }
        }
        String sql2 = "select sum(a.actual_account_balance) as actualAccountBalance from temporary_recharge_info a " +
                "where id2='" + rechargeInfo.getId() + "' and recharge_people='" + ShiroKit.getUser().getAccount() + "'";
        RechargeInfo rechargeInfo1 = sqlMapper.selectOne(sql2, RechargeInfo.class);
        Double money1 = 0.00;
        if (rechargeInfo1 != null) {
            money1 = rechargeInfo1.getActualAccountBalance();
        }
        String sql3 = "select a.service_actual_pay as serviceActualPay from " + tableName + " a where id2='" + rechargeInfo.getId2() + "' and get_time='" + getTime + "'";
        RechargeInfo rechargeInfo2 = sqlMapper.selectOne(sql3, RechargeInfo.class);
        Double money2 = rechargeInfo2.getServiceActualPay();
        Double money = subtract(money2, money1);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("totleFoldingPay", totleFoldingPay);
        model.addAttribute("totleServiceActualPay", totleServiceActualPay);
        model.addAttribute("serviceActualPay", money);
        model.addAttribute("getTime", getTime);
        model.addAttribute("payorrefund", payorrefund);

        return PREFIX + "choice_checkout_method.html";
    }

    @RequestMapping(value = "/to_choiceCheckoutMethods/{rco}")
    public String to_choiceCheckoutMethods(@PathVariable String rco, Model model) {
        String reservationsId = rco.split(",")[0];
        String customerId = rco.split(",")[1];
        String operationTime = rco.split(",")[2];
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from checkout_method_info where checkout_method!='请选择'";
        List<CheckoutMethodInfo> checkoutMethodInfo = sqlMapper.selectList(sql, CheckoutMethodInfo.class);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("checkoutMethodInfo", checkoutMethodInfo);
        return PREFIX + "choice_checkout_methods.html";
    }

    @RequestMapping(value = "/to_choiceCheckoutMethodd/{rco}")
    public String to_choiceCheckoutMethodd(@PathVariable String rco, Model model) {
        String reservationsId = rco.split(",")[0];
        String customerId = rco.split(",")[1];
        String operationTime = rco.split(",")[2];
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select * from checkout_method_info where checkout_method!='请选择'";
        List<CheckoutMethodInfo> checkoutMethodInfo = sqlMapper.selectList(sql, CheckoutMethodInfo.class);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("checkoutMethodInfo", checkoutMethodInfo);
        return PREFIX + "choice_checkout_methodd.html";
    }

    @RequestMapping(value = "/to_checkoutMethod/{id}")
    public String to_checkoutMethod(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName = tableHead + "deal_info";
        String sql = "select * from " + tableName + " where id='" + id + "'";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        String selectsql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause from " + tableName + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.deal_status!='未成交' and  t1.operation_time='" + rechargeInfo.getOperationTime() + "' " +
                "and t1.reservations_id='" + rechargeInfo.getReservationsId() + "' and  t1.get_time='" + rechargeInfo.getGetTime() + "'";
        List<PayServiceInfo> openBillList = sqlMapper.selectList(selectsql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        for (PayServiceInfo info : openBillList) {
            if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                totleFoldingPay += info.getFoldingPay();
                totleServiceActualPay += info.getServiceActualPay();
                info.setTotleReceivable(totleReceivable);
                info.setTotleFoldingPay(totleFoldingPay);
            }
        }
        String sql1 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                "  (CASE t2.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,t2.arrival_store as outpatientName,t2.customer_age as customerAge,t2.customer_phone as customerPhone ,t2.field_tc as tcName" +
                " from " + this.getTableName() + " t2 where id='" + rechargeInfo.getCustomerId() + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql1, ConsultantInfo.class);
        String selectsqlCM = "select * from checkout_method_info order by num";
        List<CheckoutMethodInfo> checkoutMethodList = sqlMapper.selectList(selectsqlCM, CheckoutMethodInfo.class);
        model.addAttribute("openBillList", openBillList);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
        model.addAttribute("customerId", rechargeInfo.getCustomerId());
        model.addAttribute("operationTime", rechargeInfo.getOperationTime());
        model.addAttribute("getTime", rechargeInfo.getGetTime());
        model.addAttribute("totleServiceActualPay", totleServiceActualPay);
        model.addAttribute("checkoutMethodList", checkoutMethodList);
        String deletesql = "delete from temporary_recharge_info where reservations_id='" + rechargeInfo.getReservationsId() + "'" +
                " and operation_time='" + rechargeInfo.getGetTime() + "'";
        sqlMapper.delete(deletesql);
        return PREFIX + "checkout_method.html";
    }

    @RequestMapping(value = "/to_specialRecharge/{id}")
    public String to_specialRecharge(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName = tableHead + "deal_info";
        String sql = "select * from " + tableName + " where id='" + id + "'";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        String selectsql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause from " + tableName + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.deal_status!='未成交' and  t1.operation_time='" + rechargeInfo.getOperationTime() + "' " +
                "and t1.reservations_id='" + rechargeInfo.getReservationsId() + "'";
        List<PayServiceInfo> openBillList = sqlMapper.selectList(selectsql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        for (PayServiceInfo info : openBillList) {
            if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                totleFoldingPay += info.getFoldingPay();
                info.setTotleReceivable(totleReceivable);
                info.setTotleFoldingPay(totleFoldingPay);
            }
        }
        String sql1 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                "  (CASE t2.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,t2.arrival_store as outpatientName,t2.customer_age as customerAge,t2.customer_phone as customerPhone ,t2.field_tc as tcName" +
                " from " + this.getTableName() + " t2 where id='" + rechargeInfo.getCustomerId() + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql1, ConsultantInfo.class);
        model.addAttribute("openBillList", openBillList);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
        model.addAttribute("customerId", rechargeInfo.getCustomerId());
        model.addAttribute("operationTime", rechargeInfo.getOperationTime());
        String deletesql = "delete from temporary_recharge_info where reservations_id='" + rechargeInfo.getReservationsId() + "'" +
                " and operation_time='" + rechargeInfo.getOperationTime() + "'";
        sqlMapper.delete(deletesql);
        return PREFIX + "special_recharge.html";
    }

    @RequestMapping(value = "/closesBalance", method = RequestMethod.POST)
    @ResponseBody
    public int closesBalance(String customerId, String reservationsId, String operationTime, HttpServletRequest request) {
        String deletesql = "delete from temporary_recharge_info where reservations_id='" + reservationsId + "'" +
                " and operation_time='" + operationTime + "'";
        return sqlMapper.delete(deletesql);
    }

    @RequestMapping(value = "/getChangeRecharge", method = RequestMethod.POST)
    @ResponseBody
    public int getCount(String id, HttpServletRequest request) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String sqlupdate = "update " + tableName + " set is_record='是',recharge_time='" + DateTimeKit.now() + "',recharge_people='" + ShiroKit.getUser().getAccount() + "' where id='" + id + "'";
        String sqlselect = "select * from " + tableName + " where id='" + id + "'";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sqlselect, RechargeInfo.class);
        String sumsql = "select sum(account_arrears) as accountArrears from " + tableName + " where customer_id='" + rechargeInfo.getCustomerId() + "'";
        RechargeInfo rechargeInfo1 = sqlMapper.selectOne(sumsql, RechargeInfo.class);
        CustomerInfo customerInfos1 = customerService.selectOneById(rechargeInfo.getCustomerId());
        String updateSqlu = "";
        Double actualAccountBalance = customerInfos1.getActualAccountBalance();
        Double giveAccountBalance = customerInfos1.getGiveAccountBalance();
        Double accountArrearss = customerInfos1.getAccountArrears();
        Double accountArrears1 = rechargeInfo1.getAccountArrears();
        if (rechargeInfo.getStatus() == 1) {
            actualAccountBalance = actualAccountBalance + rechargeInfo.getActualAccountBalance();
            accountArrearss = accountArrearss + rechargeInfo.getAccountArrears();
            giveAccountBalance = giveAccountBalance + rechargeInfo.getGiveAccountBalance();
            updateSqlu = "update " + this.getTableName() + " set actual_account_balance=" + actualAccountBalance + "," +
                    "account_arrears=" + accountArrearss + ", give_account_balance=" + giveAccountBalance + "  where reservations_id='" + customerInfos1.getReservationsId() + "'";
        } else if (rechargeInfo.getStatus() == 2) {
            Double money = rechargeInfo.getActualAccountBalance() - customerInfos1.getAccountArrears();
            if (money >= 0) {
                actualAccountBalance = actualAccountBalance + rechargeInfo.getActualAccountBalance();
                giveAccountBalance = giveAccountBalance + rechargeInfo.getGiveAccountBalance();
                updateSqlu = "update " + this.getTableName() + " set  account_arrears= 0, actual_account_balance=" + actualAccountBalance + "" +
                        ",give_account_balance=" + giveAccountBalance + "  where reservations_id='" + customerInfos1.getReservationsId() + "'";
            } else {
                accountArrearss = accountArrearss - rechargeInfo.getActualAccountBalance();
                actualAccountBalance = actualAccountBalance + rechargeInfo.getActualAccountBalance();
                giveAccountBalance = giveAccountBalance + rechargeInfo.getGiveAccountBalance();
                updateSqlu = "update " + this.getTableName() + " set account_arrears=" + accountArrearss + ", actual_account_balance=" + actualAccountBalance + "" +
                        ",give_account_balance=" + giveAccountBalance + "  where reservations_id='" + customerInfos1.getReservationsId() + "'";
            }
        }
        sqlMapper.update(updateSqlu);
        return sqlMapper.update(sqlupdate);
    }


    @RequestMapping(value = "/addBalance", method = RequestMethod.POST)
    @ResponseBody
    public List<RechargeInfo> addBalance(String customerId, String reservationsId, String getTime,
                                         String id2, String[] str, HttpServletRequest request) {
        String checkoutMethodName = "";
        String amountOfBalance = "";
        String remarks = "";
        String aa = null;

        for (int i = 0; i < str.length; i++) {
            aa = str[i];
            remarks = aa.split("#")[0];
            checkoutMethodName = aa.split("#")[1];
            amountOfBalance = aa.split("#")[2];
            String tableHead = ShiroKit.getUser().getAbbreviation();
            String id = CommUtils.getUUID();
            Random rand = new Random();
            int randomNumber = rand.nextInt(999999);
            id = id + randomNumber;

            String sql = " INSERT INTO `temporary_recharge_info` ( `status`, `customer_id`, `reservations_id`, " +
                    "  `recharge_people`, `checkout_method`, " +
                    " `actual_account_balance`, `recharge_time`, `operation_time`, " +
                    "  `remarks` , `id2` ) VALUES " +
                    "( 1, '" + customerId + "', '" + reservationsId + "', " +
                    "  '" + ShiroKit.getUser().getAccount() + "', '" + checkoutMethodName + "', " +
                    " " + amountOfBalance + ", '" + DateTimeKit.now() + "', '" + getTime + "'" +
                    ",'" + remarks + "','" + id2 + "');";
            sqlMapper.insert(sql);
        }
        String sql2 = " select checkout_method as checkoutMethod,actual_account_balance as actualAccountBalance ," +
                "remarks as remarks from temporary_recharge_info where customer_id='" + customerId + "' " +
                "and operation_time='" + getTime + "'";
        List<RechargeInfo> rechargeInfo = sqlMapper.selectList(sql2, RechargeInfo.class);
        return rechargeInfo;
    }

    @RequestMapping(value = "/addBalances", method = RequestMethod.POST)
    @ResponseBody
    public int addBalances(String customerId, String reservationsId, String operationTime, Double amountOfBalance, String checkoutMethod, String remarks, HttpServletRequest request) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String time = DateTimeKit.now();
        String id = CommUtils.getUUID();
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String sql2 = " select max(document_number) as documentNumber from " + tableName + " ";
        RefundInfo refundInfo1 = sqlMapper.selectOne(sql2, RefundInfo.class);
        Double documentNumbers2 = Double.parseDouble(refundInfo1.getDocumentNumber());
        Double documentNumbers = 0.00;
        documentNumbers = documentNumbers2;
        documentNumbers = documentNumbers + 1;
        String documentNumber = "";
        if (documentNumbers < 10) {
            documentNumber = "000000" + documentNumbers;
        } else if (documentNumbers >= 10 && documentNumbers < 100) {
            documentNumber = "00000" + documentNumbers;
        } else if (documentNumbers >= 100 && documentNumbers < 1000) {
            documentNumber = "0000" + documentNumbers;
        } else if (documentNumbers >= 1000 && documentNumbers < 10000) {
            documentNumber = "000" + documentNumbers;
        } else if (documentNumbers >= 10000 && documentNumbers < 100000) {
            documentNumber = "00" + documentNumbers;
        } else if (documentNumbers >= 100000 && documentNumbers < 1000000) {
            documentNumber = "0" + documentNumbers;
        } else if (documentNumbers >= 1000000) {
            documentNumber = "" + documentNumbers;
        }
        documentNumber = documentNumber.substring(0, documentNumber.length() - 2);
        String sql = " INSERT INTO `" + tableName + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                "  `recharge_people`, `checkout_method`, `document_number`," +
                " `actual_account_balance`, `recharge_time`, `operation_time`, " +
                "  `remarks`, `is_record` ) VALUES " +
                "('" + id + "', 1, '" + customerId + "', '" + reservationsId + "', " +
                "  '" + ShiroKit.getUser().getAccount() + "', '" + checkoutMethod + "', '" + documentNumber + "'," +
                " " + amountOfBalance + ", '" + DateTimeKit.now() + "', '" + time + "'" +
                ",'" + remarks + "','是');";
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        String sumSql = "select sum(folding_pay) as foldingPay,sum(service_actual_pay) as serviceActualPay from " + tableName1 + "   where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + operationTime + "'";
        PaymentInfo paymentInfo = sqlMapper.selectOne(sumSql, PaymentInfo.class);
        Double sp = 0.00;
        if (paymentInfo.getFoldingPay() == 0) {
            sp = 0.00;
        } else {
            sp = amountOfBalance / paymentInfo.getFoldingPay();
        }
        String updateSql1 = "update " + tableName2 + " a set a.actual_account_balance=actual_account_balance+" + amountOfBalance + " ," +
                " a.account_arrears=a.account_arrears-" + amountOfBalance + " where reservations_id='" + reservationsId + "'";
        String isql = "INSERT INTO `aggregate_info` ( `reservations_id`,`arrival_store`,`customer_id`, `money`, `get_time`) VALUES\n" +
                " ( '" + reservationsId + "','" + ShiroKit.getUser().getOutpatientName() + "','" + customerId + "', " + amountOfBalance + ", '" + DateTimeKit.now() + "');";
        String selectsql1 = "select * from " + this.getTableName() + " where id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectsql1, CustomerInfo.class);
        String sumSql2 = "select b.id,b.service_actual_pay as serviceActualPay, c.department as department from " + tableName1 + "  b  " +
                "left join pay_service_info c on b.pay_service_id=c.id where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + operationTime + "'";
        List<RechargeInfo> rechargeInfoList = sqlMapper.selectList(sumSql2, RechargeInfo.class);
        for (int i = 0; i < rechargeInfoList.size(); i++) {
            String isql1 = "INSERT INTO `reservations_aggregate_info` ( `reservations_id`,`arrival_store`,`customer_id`,`tc_name`,`doctor`,`money`, `department`, `get_time`) VALUES " +
                    " ( '" + reservationsId + "','" + ShiroKit.getUser().getOutpatientName() + "','" + customerId + "','" + customerInfo.getFieldTc() + "','" + customerInfo.getDoctor() + "', " + amountOfBalance + ", '" + rechargeInfoList.get(i).getDepartment() + "', '" + DateTimeKit.now() + "');";
            sqlMapper.insert(isql1);
        }
        sqlMapper.insert(isql);
        sqlMapper.update(updateSql1);
        String selesql = "select sum(folding_pay) as foldingPay from " + tableName1 + " where customer_id='" + customerId + "' and operation_time='" + operationTime + "' and operation_time=get_time limit 0,1";
        String selesql1 = "select sum(service_actual_pay) as serviceActualPay from " + tableName1 + " where customer_id='" + customerId + "' and operation_time='" + operationTime + "' limit 0,1";
        PaymentInfo paymentInfo1 = sqlMapper.selectOne(selesql, PaymentInfo.class);
        PaymentInfo paymentInfo2 = sqlMapper.selectOne(selesql1, PaymentInfo.class);
        Double arrears = paymentInfo1.getFoldingPay() - paymentInfo2.getServiceActualPay() - amountOfBalance;
        String insertSql = "insert into " + tableName1 + " " +
                "(id,customer_id,reservations_id,pay_service_id,tc_name,pay_service_num,service_actual_pay,remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,left_up,left_down,right_up,right_down,doctor,type_number,arrears) " +
                "select right(CONCAT(id,'" + time + "'),40),customer_id,reservations_id,pay_service_id,tc_name,pay_service_num," + amountOfBalance + ",remarks,'已成交','" + operationTime + "','" + time + "','" + ShiroKit.getSessionAttr("outpatientName") +
                "'," + paymentInfo1.getFoldingPay() + ",pay_service,left_up,left_down,right_up,right_down,doctor,type_number," + arrears + " from " + tableName1 + " where customer_id='" + customerId + "' and operation_time='" + operationTime + "' limit 0,1";
        sqlMapper.insert(insertSql);
        return sqlMapper.insert(sql);
    }

    @RequestMapping(value = "/checkOut", method = RequestMethod.POST)
    @ResponseBody
    public int checkOut(String customerId, String reservationsId, String operationTime, String getTime, HttpServletRequest request) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        String time = DateTimeKit.now();

        String sql2 = " select max(document_number) as documentNumber from " + tableName + " ";
        RefundInfo refundInfo1 = sqlMapper.selectOne(sql2, RefundInfo.class);
        String documentNumber = "";
        Double documentNumbers2 = 0.00;
        if (refundInfo1 == null) {
        } else {
            documentNumbers2 = Double.parseDouble(refundInfo1.getDocumentNumber());
        }
        Double documentNumbers = 0.00;
        documentNumbers = documentNumbers2;
        documentNumbers = documentNumbers + 1;
        if (documentNumbers < 10) {
            documentNumber = "000000" + documentNumbers;
        } else if (documentNumbers >= 10 && documentNumbers < 100) {
            documentNumber = "00000" + documentNumbers;
        } else if (documentNumbers >= 100 && documentNumbers < 1000) {
            documentNumber = "0000" + documentNumbers;
        } else if (documentNumbers >= 1000 && documentNumbers < 10000) {
            documentNumber = "000" + documentNumbers;
        } else if (documentNumbers >= 10000 && documentNumbers < 100000) {
            documentNumber = "00" + documentNumbers;
        } else if (documentNumbers >= 100000 && documentNumbers < 1000000) {
            documentNumber = "0" + documentNumbers;
        } else if (documentNumbers >= 1000000) {
            documentNumber = "" + documentNumbers;
        }
        documentNumber = documentNumber.substring(0, documentNumber.length() - 2);
        String times = time;
        if (operationTime.equals(getTime)) {
            times = operationTime;
        }
        String sql = " INSERT INTO `" + tableName + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                "  `recharge_people`, `checkout_method`, `document_number` " +
                " ,`actual_account_balance`, `recharge_time`, `operation_time`, " +
                "  `remarks`, `is_record`, `id2` ) " +
                " select right(CONCAT(id,operation_time),40) , `status`, `customer_id`, `reservations_id`, " +
                " `recharge_people`, `checkout_method`, '" + documentNumber + "' ," +
                "  `actual_account_balance`, `recharge_time`, '" + times + "', " +
                " `remarks`,'是', `id2` from temporary_recharge_info where reservations_id='" + reservationsId + "'" +
                " and operation_time='" + getTime + "'";
        String deletesql = "delete from temporary_recharge_info where reservations_id='" + reservationsId + "'" +
                " and operation_time='" + getTime + "'";
        sqlMapper.insert(sql);

        //根据预约ID 和gettime时间 分组得到结账表中客户的实收金额
        String accountSqL = "select sum(actual_account_balance) as actualAccountBalance from  "+tableName+" where  reservations_id='" + reservationsId + "' and operation_time='" + times + "' group by reservations_id ,operation_time ";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(accountSqL,RechargeInfo.class);
      //插入建档人业绩表中
        String id = CommUtils.getUUID();
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        String chSql="select b.id as id,b.reservations_id as reservationsId,b.archiving as archiving ,a.department as department from reservations_info a left join reservations_arching_info b on a.id=b.reservations_id " +
                " where b.reservations_id='"+reservationsId+"' and a.archiving = b.archiving and a.filing_time = b.filing_time limit 0,1";
        ReservationsArchingInfo ainfo = sqlMapper.selectOne(chSql,ReservationsArchingInfo.class);
        if(ainfo!=null){
            String setSql = " INSERT INTO  arching_performance_info (id, reservations_id, " +
                    "  archiving_id, recharge_time " +
                    " ,operation_people, account, " +
                    "  dept)  VALUES ('" + id + "','"+ainfo.getReservationsId()+"', '" + ainfo.getId() + "', '" + DateTimeKit.now() + "', " +
                    "  '" + ShiroKit.getUser().getAccount() + "', '" + rechargeInfo.getActualAccountBalance() + "', '" + ainfo.getDepartment()+ "');";
            sqlMapper.insert(setSql);
        }
//        String sumSql = "select  sum(folding_pay)  as foldingPay,sum(service_actual_pay) as serviceActualPay from " + tableName1 + "   where reservations_id='" + reservationsId + "' " +
//                "and customer_id='" + customerId + "';";
//        PaymentInfo paymentInfo = sqlMapper.selectOne(sumSql, PaymentInfo.class);
        String sumSql_1 = "select  sum(folding_pay)  as foldingPay,sum(service_actual_pay) as serviceActualPay," +
                " sum(arrears) as arrears  from " + tableName1 + "   where reservations_id='" + reservationsId + "' " +
                " and get_time='" + getTime + "'";
        PaymentInfo paymentInfo_1 = sqlMapper.selectOne(sumSql_1, PaymentInfo.class);
//        Double foldMoney = paymentInfo_1.getFoldingPay();  //每单的折后价合计
        Double amountOfBalances = paymentInfo_1.getServiceActualPay();
//        String sumSql_2 = "select  sum(folding_pay)  as foldingPay,sum(service_actual_pay) as serviceActualPay," +
//                " sum(arrears) as arrears  from " + tableName1 + "   where reservations_id='" + reservationsId + "' " +
//                " and operation_time='" + operationTime + "'";
//        PaymentInfo paymentInfo_2 = sqlMapper.selectOne(sumSql_2, PaymentInfo.class);
//        Double ServiceActualPay = paymentInfo_2.getServiceActualPay();  //每单的实际付款金额
//        Double arrears = foldMoney - ServiceActualPay;   //每单的欠款
        if (!operationTime.equals(getTime)) {
            service.updateGetTime(reservationsId, getTime, time);
            getTime = time;
        }
        /*String sqlone = "select * from " + tableName1 + " where  operation_time='" + operationTime + "' and reservations_id='" + reservationsId + "' and operation_time=get_time ";
        List<PayServiceInfo> openBillList1 = sqlMapper.selectList(sqlone, PayServiceInfo.class);//查询初始开单成交记录
        String sqltwo = "select * from " + tableName1 + " where  operation_time='" + operationTime + "' and reservations_id='" + reservationsId + "' and deal_status='已成交'  order by get_time desc limit 0," + openBillList1.size() + "";
        List<PayServiceInfo> openBillList2 = sqlMapper.selectList(sqltwo, PayServiceInfo.class);//查询最近已成交的记录
        String sqlthr = "select * from " + tableName1 + " where operation_time='" + operationTime + "' and get_time='" + getTime + "' and reservations_id='" + reservationsId + "'";
        List<PayServiceInfo> openBillList3 = sqlMapper.selectList(sqlthr, PayServiceInfo.class);//查询当前记录
        for (PayServiceInfo info : openBillList2) {
            if (info != null) {
                for (PayServiceInfo info1 : openBillList3) {
                    if (info1 != null) {
                        if (info1.getId2().equals(info.getId2())) {
                            if (info1.getIsDo() != null) {
                                if (info1.getIsDo() == 1) {//不做了
                                    String sqlup = "update " + tableName1 + " set arrears=" + info.getArrears() + " where operation_time='" + operationTime + "' " +
                                            "and reservations_id='" + reservationsId + "' and id2='" + info.getId2() + "' and get_time='" + getTime + "' ";
                                    sqlMapper.update(sqlup);
                                } else if (info1.getIsDo() == 2) {//继续做
                                    String sqlup = "update " + tableName1 + " set arrears=" + (info.getArrears() - info1.getServiceActualPay()) + " where operation_time='" + operationTime + "' " +
                                            "and reservations_id='" + reservationsId + "' and id2='" + info.getId2() + "' and get_time='" + getTime + "' ";
                                    sqlMapper.update(sqlup);

                                    String updateSqlcu = "update " + tableName2 + " a  " +
                                            "set a.account_arrears= account_arrears-'" + info1.getServiceActualPay() + "' where reservations_id='" + reservationsId + "'";
                                    sqlMapper.update(updateSqlcu);
                                } else if (info1.getIsDo() == 3) {//补款
                                    String sqlup = "update " + tableName1 + " set arrears=" + (info.getArrears() - info1.getServiceActualPay()) + " where operation_time='" + operationTime + "' " +
                                            "and reservations_id='" + reservationsId + "' and id2='" + info.getId2() + "' and get_time='" + getTime + "' ";
                                    sqlMapper.update(sqlup);

                                    String updateSqlcu = "update " + tableName2 + " a  " +
                                            "set a.account_arrears=account_arrears-'" + info1.getServiceActualPay() + "' where reservations_id='" + reservationsId + "'";
                                    sqlMapper.update(updateSqlcu);
                                } else if (info1.getIsDo() == 4) {//退项目
                                    String sqlup = "update " + tableName1 + " set arrears=0" +
                                            " where operation_time='" + operationTime + "' " +
                                            "and reservations_id='" + reservationsId + "' and id2='" + info.getId2() + "' and get_time='" + getTime + "' ";
                                    sqlMapper.update(sqlup);

                                    String updateSqlcu = "update " + tableName2 + " a  " +
                                            "set a.account_arrears=account_arrears-'" + info.getArrears() + "' where reservations_id='" + reservationsId + "'";
                                    sqlMapper.update(updateSqlcu);
                                }
                            }
                        }
                    }
                }
            }
        }
        if (openBillList2.size() == 0) {
            for (PayServiceInfo info1 : openBillList3) {
                if (info1.getIsDo() == 5) {  //结账
                    String updateSqlcu = "update " + tableName2 + " a  " +
                            "set a.account_arrears=account_arrears+'" + info1.getArrears() + "' where reservations_id='" + reservationsId + "'";
                    sqlMapper.update(updateSqlcu);
                }
            }
        }*/
        //修改欠费
        String updateSqlcu = "update " + this.getTableName() + " a set a.account_arrears=( " +
                " select sum(b.arrears) from "+ tableName1 + " b where b.operation_time='"+operationTime+"' " +
                " and a.reservations_id=b.reservations_id )  where a.reservations_id='"+reservationsId+"'";
        sqlMapper.update(updateSqlcu);

        String sqlCustau = "select * from " + tableName1 + " a left join pay_service_info b on (a.pay_service_id=b.id) " +
                " left join " + this.getTableName() + " c on(a.customer_id=c.id)" +
                " where a.operation_time='" + operationTime + "' and a.get_time='" + getTime + "' and a.reservations_id='" + reservationsId + "'";
        List<PayServiceInfo> openct = sqlMapper.selectList(sqlCustau, PayServiceInfo.class);//查询当前记录
        Integer isdo = 0;
        for (PayServiceInfo inf : openct) {
            isdo = inf.getIsDo();
        }
        if (isdo == 5) {    //结账
            if (openct.size() == 1) {
                for (PayServiceInfo inf : openct) {
                    if ("初到".equals(inf.getXcustomerStatus())) {
                        if ((inf.getPayServiceName()).indexOf("洁牙") != -1) {  //是洁牙的单,为初到洁牙
                            String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='初到洁牙',deal_status='已成交' " +
                                    "where id='" + customerId + "'  ";
                            sqlMapper.update(updatesqlct);
                        } else {           //不是洁牙的单,为初治
                            String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='初治',deal_status='已成交' " +
                                    " where id='" + customerId + "'  ";
                            sqlMapper.update(updatesqlct);
                        }
                    } else if ((inf.getXcustomerStatus()).indexOf("复到") != -1) {
                        String sqlsel = "select * from " + tableName1 + "  where reservations_id='" + reservationsId + "' " +
                                " and left(operation_time,10)='" + operationTime.substring(0, 10) + "'  and deal_status='已成交'  group by operation_time";
                        List<PayServiceInfo> sqlsell = sqlMapper.selectList(sqlsel, PayServiceInfo.class);  //今天开过的单

                        String sqlsel2 = "select * from " + tableName1 + "  where reservations_id='" + reservationsId + "' " +
                                " and left(operation_time,10)<'" + operationTime.substring(0, 10) + "'  and deal_status='已成交'  group by operation_time";
                        List<PayServiceInfo> sqlsell2 = sqlMapper.selectList(sqlsel2, PayServiceInfo.class); //做过两次以上项目
                        if (sqlsell.size() > 0 || sqlsell2.size() > 1) {  //复诊，今天开过一单，或者做过两次以上项目，为再消
                            String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='再消',deal_status='已成交' " +
                                    "where id='" + customerId + "'  ";
                            sqlMapper.update(updatesqlct);
                        } else {
                            if ((inf.getPayServiceName()).indexOf("洁牙") != -1) {  //是洁牙的单
                                /*String sqljy = "select * from " + tableName1 + " a left join pay_service_info b on (a.pay_service_id=b.id) " +
                                        " left join " + this.getTableName() + " c on(a.customer_id=c.id)" +
                                        " where  a.reservations_id='" + reservationsId + "'" +
                                        "  and a.deal_status='已成交' and a.is_do='5' order by operation_time limit 0,1 ";
                                PayServiceInfo listjy = sqlMapper.selectOne(sqljy, PayServiceInfo.class);//查询最近已成交的记录
                                if ((listjy.getPayServiceName()).indexOf("洁牙") != -1) {  //并且上次为洁牙的单*/
                                String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='复到洁牙',deal_status='已成交' " +
                                        "where id='" + customerId + "'  ";
                                sqlMapper.update(updatesqlct);
                                /*} else {       //上次不是洁牙的单,为复到初治
                                    String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='复到初治',deal_status='已成交' " +
                                            " where id='" + customerId + "'  ";
                                    sqlMapper.update(updatesqlct);
                                }*/
                            } else {      //不是洁牙的单,为复到初治
                                String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='复到初治',deal_status='已成交' " +
                                        " where id='" + customerId + "'  ";
                                sqlMapper.update(updatesqlct);
                            }
                        }
                    }
                }
            } else {           //不是洁牙的单,为初治
                for (PayServiceInfo inf : openct) {
                    if ("初到".equals(inf.getXcustomerStatus())) {
                        String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='初治',deal_status='已成交' " +
                                " where id='" + customerId + "'  ";
                        sqlMapper.update(updatesqlct);
                    } else if ((inf.getXcustomerStatus()).indexOf("复到") != -1) {    //复到
                        String sqlsel = "select * from " + tableName1 + "  where reservations_id='" + reservationsId + "' " +
                                " and left(operation_time,10)='" + operationTime.substring(0, 10) + "'  and deal_status='已成交'  group by operation_time";
                        List<PayServiceInfo> sqlsell = sqlMapper.selectList(sqlsel, PayServiceInfo.class);   //今天开过的单

                        String sqlsel2 = "select * from " + tableName1 + "  where reservations_id='" + reservationsId + "' " +
                                " and left(operation_time,10)<'" + operationTime.substring(0, 10) + "'  and deal_status='已成交'  group by operation_time";
                        List<PayServiceInfo> sqlsell2 = sqlMapper.selectList(sqlsel2, PayServiceInfo.class); //做过两次以上项目
                        if (sqlsell.size() > 0 || sqlsell2.size() > 1) {  //复诊，今天开过一单，或者做过两次以上项目，为再消
                            String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='再消',deal_status='已成交' " +
                                    "where id='" + customerId + "'  ";
                            sqlMapper.update(updatesqlct);
                        } else {          //不是洁牙的单,为复到初治
                            String updatesqlct = "update " + this.getTableName() + " set xcustomer_status='复到初治',deal_status='已成交' " +
                                    " where id='" + customerId + "'  ";
                            sqlMapper.update(updatesqlct);
                        }
                    }

                }
            }
        }

        String updatesql = "update " + tableName1 + " set deal_status='已成交' " +
                " where reservations_id='" + reservationsId + "' and get_time='" + getTime + "'";
        String updateSql2 = "update reservations_info set deal_status='已成交',money=money+" + amountOfBalances + " where id='" + reservationsId + "' ";
        String updateSql1 = "update " + tableName2 + " a set a.actual_account_balance=actual_account_balance+" + amountOfBalances
                + " ,a.deal_status='已成交'  where reservations_id='" + reservationsId + "'";
        sqlMapper.update(updateSql1);
        String selectCustomerStatus = "select id,customer_status as customerStatus,field_tc as FieldTc,doctor as doctor from " + this.getTableName() + "" +
                " where id='" + customerId + "' ";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectCustomerStatus, CustomerInfo.class);
        if ("复诊".equals(customerInfo.getCustomerStatus())) {
            String updatesql3 = "update " + tableName2 + " a set deal_status='已成交' , a.customer_status='老客' where id='" + customerId + "' ";
            sqlMapper.update(updatesql3);
        } else {
            String updatesql3 = "update " + this.getTableName() + " set deal_status='已成交' where id='" + customerId + "'  ";
            sqlMapper.update(updatesql3);
        }
        String selectsql21 = "select sum(actual_account_balance) as actualAccountBalance from " + tableName + " where reservations_id='" + reservationsId + "' and operation_time='" + getTime + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(selectsql21, ConsultantInfo.class);
        String isql = "INSERT INTO `aggregate_info` ( `reservations_id`, `arrival_store`,`customer_id`, `money`, `get_time`) VALUES\n" +
                " ( '" + reservationsId + "','" + ShiroKit.getUser().getOutpatientName() + "','" + customerId + "', " + consultantInfo.getActualAccountBalance() + ", '" + operationTime + "');";
        String sumSql2 = "select b.id,b.service_actual_pay as serviceActualPay, c.department as department from " + tableName1 + "  b  " +
                "left join pay_service_info c on b.pay_service_id=c.id where reservations_id='" + reservationsId + "' " +
                "and get_time='" + getTime + "'";
        List<RechargeInfo> rechargeInfoList = sqlMapper.selectList(sumSql2, RechargeInfo.class);
        for (int i = 0; i < rechargeInfoList.size(); i++) {
            String isql1 = "INSERT INTO `reservations_aggregate_info` ( `reservations_id`,`arrival_store`,`customer_id`,`tc_name`,`doctor`,`money`, `department`, `get_time`) VALUES " +
                    " ( '" + reservationsId + "','" + ShiroKit.getUser().getOutpatientName() + "','" + customerId + "','" + customerInfo.getFieldTc() + "','" + customerInfo.getDoctor() + "', " + rechargeInfoList.get(i).getServiceActualPay() + ", '" + rechargeInfoList.get(i).getDepartment() + "', '" + DateTimeKit.now() + "');";
            sqlMapper.insert(isql1);
        }
        sqlMapper.insert(isql);
        sqlMapper.update(updateSql2);
        sqlMapper.update(updatesql);
        return sqlMapper.delete(deletesql);
    }

    @RequestMapping(value = "/checkOutd", method = RequestMethod.POST)
    @ResponseBody
    public int checkOutd(String customerId, String reservationsId, String operationTime, HttpServletRequest request) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String sql2 = " select max(document_number) as documentNumber from " + tableName + " ";
        RefundInfo refundInfo1 = sqlMapper.selectOne(sql2, RefundInfo.class);
        Double documentNumbers2 = Double.parseDouble(refundInfo1.getDocumentNumber());
        Double documentNumbers = 0.00;
        documentNumbers = documentNumbers2;
        documentNumbers = documentNumbers + 1;
        String documentNumber = "";
        if (documentNumbers < 10) {
            documentNumber = "000000" + documentNumbers;
        } else if (documentNumbers >= 10 && documentNumbers < 100) {
            documentNumber = "00000" + documentNumbers;
        } else if (documentNumbers >= 100 && documentNumbers < 1000) {
            documentNumber = "0000" + documentNumbers;
        } else if (documentNumbers >= 1000 && documentNumbers < 10000) {
            documentNumber = "000" + documentNumbers;
        } else if (documentNumbers >= 10000 && documentNumbers < 100000) {
            documentNumber = "00" + documentNumbers;
        } else if (documentNumbers >= 100000 && documentNumbers < 1000000) {
            documentNumber = "0" + documentNumbers;
        } else if (documentNumbers >= 1000000) {
            documentNumber = "" + documentNumbers;
        }
        documentNumber = documentNumber.substring(0, documentNumber.length() - 2);
        String sql = " INSERT INTO `" + tableName + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                "  `recharge_people`, `checkout_method`, `document_number`," +
                " `actual_account_balance`, `recharge_time`, `operation_time`, " +
                "  `remarks`, `is_record` ) " +
                " select right(CONCAT(id,operation_time),40) , `status`, `customer_id`, `reservations_id`, " +
                " `recharge_people`, `checkout_method`,'" + documentNumber + "', " +
                "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                " `remarks`,'是' from temporary_recharge_info where reservations_id='" + reservationsId + "'" +
                " and operation_time='" + operationTime + "'";
        String deletesql = "delete from temporary_recharge_info where reservations_id='" + reservationsId + "'" +
                " and operation_time='" + operationTime + "'";
        sqlMapper.insert(sql);
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        String sumSql = "select sum(folding_pay) as foldingPay,sum(service_actual_pay) as serviceActualPay from " + tableName1 + "   where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + operationTime + "'";
        PaymentInfo paymentInfo = sqlMapper.selectOne(sumSql, PaymentInfo.class);
        Double amountOfBalances = paymentInfo.getServiceActualPay();
        String updatesql = "update " + tableName1 + " set deal_status='已成交'," +
                "service_actual_pay= " + amountOfBalances + "" +
                " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
        String updateSql = "update " + this.getTableName() + " set deal_status='未成交'   where id='" + customerId + "';";
        String updateSql2 = "update reservations_info set deal_status='已成交' where id='" + reservationsId + "';";
        String updateSql1 = "update " + tableName2 + " a set a.actual_account_balance=actual_account_balance+" + amountOfBalances + " " +
                " ,account_arrears=account_arrears-'" + amountOfBalances + "' where reservations_id='" + reservationsId + "';";
        sqlMapper.update(updateSql1);
        String selectCustomerStatus = "select id,customer_status as customerStatus,field_tc as FieldTc,doctor as doctor from " + this.getTableName() + "" +
                " where id='" + customerId + "' ";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectCustomerStatus,
                CustomerInfo.class);
        String selectsql21 = "select sum(actual_account_balance) as actualAccountBalance from " + tableName + " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(selectsql21, ConsultantInfo.class);
        String isql = "INSERT INTO `aggregate_info` ( `reservations_id`, `arrival_store`,`customer_id`, `money`, `get_time`) VALUES\n" +
                " ( '" + reservationsId + "','" + ShiroKit.getUser().getOutpatientName() + "','" + customerId + "', " + consultantInfo.getActualAccountBalance() + ", '" + operationTime + "');";
        String sumSql2 = "select b.id,b.service_actual_pay as serviceActualPay, c.department as department from " + tableName1 + "  b  " +
                "left join pay_service_info c on b.pay_service_id=c.id where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + operationTime + "'";
        List<RechargeInfo> rechargeInfoList = sqlMapper.selectList(sumSql2, RechargeInfo.class);
        for (int i = 0; i < rechargeInfoList.size(); i++) {
            String isql1 = "INSERT INTO `reservations_aggregate_info` ( `reservations_id`,`arrival_store`,`customer_id`,`tc_name`,`doctor`,`money`, `department`, `get_time`) VALUES " +
                    " ( '" + reservationsId + "','" + ShiroKit.getUser().getOutpatientName() + "','" + customerId + "','" + customerInfo.getFieldTc() + "','" + customerInfo.getDoctor() + "', " + rechargeInfoList.get(i).getServiceActualPay() + ", '" + rechargeInfoList.get(i).getDepartment() + "', '" + DateTimeKit.now() + "');";
            sqlMapper.insert(isql1);
        }
        sqlMapper.insert(isql);
        sqlMapper.update(updateSql2);
        sqlMapper.update(updateSql);
        sqlMapper.update(updatesql);
        return sqlMapper.delete(deletesql);
    }

    @RequestMapping(value = "/addPayments", method = RequestMethod.POST)
    @ResponseBody
    public int addPayment(String id, HttpServletRequest request) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        String uuid = CommUtils.getUUID();
        String selectSql = "select * from " + tableName1 + " where id='" + id + "'";
        PaymentInfo info = sqlMapper.selectOne(selectSql, PaymentInfo.class);
        String suminfoSql = "select sum(folding_pay) as foldingPay,sum(service_actual_pay) as serviceActualPay " +
                "from " + tableName1 + " where reservations_id='" + info.getReservationsId() + "' and operation_time='" + info.getOperationTime() + "' ";
        PaymentInfo sumInfo = sqlMapper.selectOne(suminfoSql, PaymentInfo.class);
        info.setFoldingPay(sumInfo.getFoldingPay());
        info.setServiceActualPay(sumInfo.getServiceActualPay());
        Double accountArrears = info.getFoldingPay() - info.getServiceActualPay();
        String sql1 = "INSERT INTO " + tableName + " (`id`,`customer_id`,`status`,`reservations_id`, `record_people`, `actual_account_balance`,`account_arrears`,`record_time`,`operation_time`) VALUES" +
                "('" + uuid + "','" + info.getCustomerId() + "'," + 1 + ",'" + info.getReservationsId() + "','" + ShiroKit.getUser().getAccount() + "" +
                "'," + info.getServiceActualPay() + ", " + accountArrears + " ,'" + DateTimeKit.now() + "','" + info.getOperationTime() + "');";
        String sumSql = "select sum(folding_pay) as foldingPay from " + tableName1 + " where reservations_id='" + info.getReservationsId() + "' and operation_time='" + info.getOperationTime() + "'";
        PaymentInfo paymentInfo = sqlMapper.selectOne(sumSql, PaymentInfo.class);
        Double sp = 0.00;
        if (paymentInfo.getFoldingPay() == 0) {
            sp = 0.00;
        } else {
            sp = info.getServiceActualPay() / paymentInfo.getFoldingPay();
        }
        String sql = "update " + tableName1 + " set deal_status='已成交',service_actual_pay=folding_pay * " + sp + "" +
                " where reservations_id='" + info.getReservationsId() + "' and operation_time='" + info.getOperationTime() + "'";
        String updateSql = "update " + this.getTableName() + " set deal_status='已成交' where id='" + info.getCustomerId() + "'  ";
        String updateSql2 = "update reservations_info set deal_status='已成交' where  id='" + info.getReservationsId() + "' ";
        String selectCustomerStatus = "select id,customer_status as customerStatus from " + this.getTableName() + " where id='" + info.getCustomerId() + "' ";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectCustomerStatus, CustomerInfo.class);
        if ("复诊".equals(customerInfo.getCustomerStatus())) {
            String updatesql3 = "update " + tableName2 + " a set a.customer_status='老客'  where id='" + info.getCustomerId() + "'  ";
            sqlMapper.update(updatesql3);
        }
        sqlMapper.update(updateSql2);
        sqlMapper.update(updateSql);
        sqlMapper.insert(sql1);
        sqlMapper.update(sql);
        String sqlupdate = "update " + tableName + " set is_record='是',recharge_time='" + DateTimeKit.now() + "',recharge_people='" + ShiroKit.getUser().getAccount() + "' where id='" + id + "'";
        String sqlselect = "select * from " + tableName + " where reservations_id='" + customerInfo.getReservationsId() + "' and operation_time='" + info.getOperationTime() + "'";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sqlselect, RechargeInfo.class);
        String sumsql = "select sum(account_arrears) as accountArrears from " + tableName + " where customer_id='" + rechargeInfo.getCustomerId() + "'";
        RechargeInfo rechargeInfo1 = sqlMapper.selectOne(sumsql, RechargeInfo.class);
        CustomerInfo customerInfos1 = customerService.selectOneById(rechargeInfo.getCustomerId());
        String updateSqlu = "";
        Double actualAccountBalance = customerInfos1.getActualAccountBalance();
        Double giveAccountBalance = customerInfos1.getGiveAccountBalance();
        Double accountArrearss = customerInfos1.getAccountArrears();
        Double accountArrears1 = rechargeInfo1.getAccountArrears();
        if (rechargeInfo.getStatus() == 1) {
            actualAccountBalance = actualAccountBalance + rechargeInfo.getActualAccountBalance();
            accountArrearss = accountArrearss + rechargeInfo.getAccountArrears();
            giveAccountBalance = giveAccountBalance + rechargeInfo.getGiveAccountBalance();
            updateSqlu = "update " + this.getTableName() + " set actual_account_balance=" + actualAccountBalance + "," +
                    "account_arrears=" + accountArrearss + ", give_account_balance=" + giveAccountBalance + "  where reservations_id='" + customerInfos1.getReservationsId() + "'";
        } else if (rechargeInfo.getStatus() == 2) {
            Double money = rechargeInfo.getActualAccountBalance() - customerInfos1.getAccountArrears();
            if (money >= 0) {
                actualAccountBalance = actualAccountBalance + rechargeInfo.getActualAccountBalance();
                giveAccountBalance = giveAccountBalance + rechargeInfo.getGiveAccountBalance();
                updateSqlu = "update " + this.getTableName() + " set  account_arrears= 0, actual_account_balance=" + actualAccountBalance + "" +
                        ",give_account_balance=" + giveAccountBalance + "  where reservations_id='" + customerInfos1.getReservationsId() + "'";
            } else {
                accountArrearss = accountArrearss - rechargeInfo.getActualAccountBalance();
                actualAccountBalance = actualAccountBalance + rechargeInfo.getActualAccountBalance();
                giveAccountBalance = giveAccountBalance + rechargeInfo.getGiveAccountBalance();
                updateSqlu = "update " + this.getTableName() + " set account_arrears=" + accountArrearss + ", actual_account_balance=" + actualAccountBalance + "" +
                        ",give_account_balance=" + giveAccountBalance + "  where reservations_id='" + customerInfos1.getReservationsId() + "'";
            }
        }
        sqlMapper.update(updateSqlu);
        return sqlMapper.update(sqlupdate);
    }


    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }

    /**
     * 统计查询
     */
    @RequestMapping(value = "/rechargeTotal", method = RequestMethod.GET)
    @ResponseBody
    public List<RechargeInfo> rechargeTotal(String startArrivalTimeTwo, String endArrivalTimeTwo,
                                            String dealStatus, String oldOneChannelValue, String reach,
                                            String oldTwoChannelValue, String oldThrChannelValue) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String tableName1 = tableHead + "deal_info";
        String contitionInfo1 = " ";
        if (StringUtils.isNoneEmpty(startArrivalTimeTwo)) {  //结账时间
            contitionInfo1 += " and t.get_time >= '" + startArrivalTimeTwo + "' ";
        }
        if (StringUtils.isNoneEmpty(endArrivalTimeTwo)) {
            contitionInfo1 += " and t.get_time <= '" + endArrivalTimeTwo + "' ";
        }
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            contitionInfo1 += "  and (t1.customer_name like CONCAT('%','" + reach + "','%') or t1.customer_phone like CONCAT('%','" + reach + "','%') or\n" +
                    " t1.wechat_number like CONCAT('%','" + reach + "','%') or t1.medical_record_number like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtils.isNoneEmpty(dealStatus)) {  //状态
            contitionInfo1 += " and t.deal_Status = '" + dealStatus + "' ";
        }
        if (StringUtils.isNoneEmpty(oldOneChannelValue) && !oldOneChannelValue.equals("请选择")) {
            contitionInfo1 += " and t1.one_channel = '" + oldOneChannelValue + "' ";
        }
        if (StringUtils.isNoneEmpty(oldTwoChannelValue) && !oldTwoChannelValue.equals("请选择")) {
            contitionInfo1 += " and t1.two_Channel = '" + oldTwoChannelValue + "' ";
        }
        if (StringUtils.isNoneEmpty(oldThrChannelValue) && !oldThrChannelValue.equals("请选择")) {
            contitionInfo1 += " and t1.thr_Channel = '" + oldThrChannelValue + "' ";
        }
        contitionInfo1 += " and t.deal_status='已成交' ";

        String sql = "select  count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status != '作废') t " +

                "union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join  " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status = '初到洁牙') t " +

                "union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join  " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status = '初治') t " +

                " union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join  " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status  = '复到洁牙') t " +

                " union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join  " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status  = '复到初治') t " +

                " union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join  " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status  = '流失') t "+

                " union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  from " + tableName1 + " t  left join  " + tableName + " t1 on t.customer_id=t1.id\n" +
                " where 1=1 " + contitionInfo1 + "  AND t1.xcustomer_status  = '再消') t ";

        List<RechargeInfo> ToalList = sqlMapper.selectList(sql, RechargeInfo.class);
        return ToalList;
    }

    /**
     * 修改时间  通过客户ID 进行修改
     */
    @RequestMapping(value = "/updateGetTime", method = RequestMethod.POST)
    @ResponseBody
    public int updateGetTime(String customerId, String getTime, String updateTime) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String tableName1 = tableHead + "deal_info";
        String sql1 = "update " + tableName + " t set t.recharge_time = '" + updateTime + "',t.recharges_time =null," +
                " t.operation_time = '" + updateTime + "' where t.customer_id = '" + customerId + "' and t.operation_time ='" + getTime + "'  ";
        sqlMapper.update(sql1);

        String sql = "select * from  " + tableName1 + " t where t.customer_id = '" + customerId + "' and t.get_time ='" + getTime + "' limit 0,1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        String sql2 = "";
        if (rechargeInfo.getOperationTime().equals(rechargeInfo.getGetTime())) {
            sql2 = "update " + tableName1 + " t set t.get_time = '" + updateTime + "',t.operation_time = '" + updateTime + "' where t.customer_id = '" + customerId + "' and t.get_time ='" + getTime + "' ";
        } else {
            sql2 = "update " + tableName1 + " t set t.get_time = '" + updateTime + "' where t.customer_id = '" + customerId + "' and t.get_time ='" + getTime + "' ";
        }
        sqlMapper.update(sql2);

        String sql3 = "update aggregate_info t set t.get_time = '" + updateTime + "' where t.customer_id = '" + customerId + "' and t.get_time ='" + getTime + "'";
        sqlMapper.update(sql3);

        String sql4 = "update reservations_aggregate_info t set t.get_time = '" + updateTime + "' where t.customer_id = '" + customerId + "' and t.get_time ='" + getTime + "' ";

        return sqlMapper.update(sql4);
    }

    /**
     * 修改时间跳转界面
     */
    @RequestMapping(value = "/update_time/{ids}")
    public String updateTime(@PathVariable String ids, Model model) {
        String customerId = ids.split(",")[0];
        String getTime = ids.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("getTime", getTime);
        model.addAttribute("customerId", customerId);
        return PREFIX + "/update_time.html";
    }

    /**
     * 修改时间跳转界面
     */
    @RequestMapping(value = "/update_documentNumber/{ids}")
    public String to_updateDocumentNumber(@PathVariable String ids, Model model) {
        String customerId = ids.split(",")[0];
        String getTime = ids.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql1 = "select id as id,reservations_id as reservationsId, medical_record_number as medicalRecordNumber, arrival_store as arrivalStore,customer_name as customerName,customer_age as customerAge," +
                " customer_phone as customerPhone ,  " +
                " (\n" +
                "        CASE customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "        '男'\n" +
                "        WHEN 2 THEN\n" +
                "        '女'\n" +
                "        ELSE\n" +
                "        '未知'\n" +
                "        END\n" +
                "        ) as sex  from " + this.getTableName() + " where id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql = "select * from " + tableName + " where customer_id='" + customerId + "' and operation_time='" + getTime + "' limit 0,1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        model.addAttribute("rechargeInfo", rechargeInfo);
        model.addAttribute("getTime", getTime);
        model.addAttribute("customerId", customerId);
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "/update_document_number.html";
    }


    @RequestMapping(value = "/dealInfo_import")
    public String to_import(Model model) {
        return PREFIX + "dealInfo_import.html";
    }

    /**
     * 修改开始时间，成交状态
     */

  /*  @RequestMapping(value = "/uploadLoad")
    public String importUploadLoad(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    DecimalFormat df = new DecimalFormat("0");

                    Cell xh1 = hssfRow.getCell(0); //姓名
                    Cell xh2 = hssfRow.getCell(1);  //病历号
                    Cell xh3 = hssfRow.getCell(2);  //手机号
                    Cell xh4 = hssfRow.getCell(3);//开始时间
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }

                    String sqladd = "update " + this.getTableName() + " set deal_status ="+xh4s+", start_time="+xh2s+"" +
                            " where customer_phone="+xh1s+" and arrival_time="+xh3s;
                    sqlMapper.update(sqladd);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }

*/

    /**
     * 修改预约表渠道和更进人
     */
/*

   @RequestMapping(value = "/uploadLoad")
    public String importUploadLoad(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    DecimalFormat df = new DecimalFormat("0");

                    Cell xh1 = hssfRow.getCell(0); //姓名
                    Cell xh2 = hssfRow.getCell(1);  //病历号
                    Cell xh3 = hssfRow.getCell(2);  //手机号
                    Cell xh4 = hssfRow.getCell(3);//开始时间
                    Cell xh5 = hssfRow.getCell(4);//开始时间
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }

                    String sqladd = "update reservations_info set channel ="+xh4s+", follower="+xh5s+"" +
                            " where customer_phone="+xh2s+" and filing_time="+xh3s +" and outpatient_name ='德米店'";
                    sqlMapper.update(sqladd);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }

*/

    /**
     * 修改预约表预约时间
     */


   /** @RequestMapping(value = "/uploadLoad")
    public String importUploadLoad(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    DecimalFormat df = new DecimalFormat("0");

                    Cell xh1 = hssfRow.getCell(0); //姓名
                    Cell xh2 = hssfRow.getCell(1);  //病历号
                    Cell xh3 = hssfRow.getCell(2);  //手机号
                    Cell xh4 = hssfRow.getCell(3);//开始时间
                    Cell xh5 = hssfRow.getCell(4);//开始时间
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }

                    String sqladd = "update reservations_info set appointment_time =" + xh5s + " " +
                            " where customer_phone=" + xh2s + " and filing_time=" + xh4s + " and outpatient_name ='德米店'";
                    sqlMapper.update(sqladd);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }
   */


    /**
     * 导入预约查询明细数据
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/customerInfo_import")
    public String customerInfoImport(Model model) {
        return PREFIX + "customerInfo_import.html";
    }

    /**
     * 导入预约查询
     */
   @RequestMapping(value = "/uploadLoad")
    public String importUploadLoad(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    DecimalFormat df = new DecimalFormat("0");

                    Cell xh1 = hssfRow.getCell(0); //姓名
                    Cell xh2 = hssfRow.getCell(1);  //病历号
                    String xh2hs = df.format(xh2.getNumericCellValue());

                    Cell xh3 = hssfRow.getCell(2);  //手机号
                    String xh3hs = df.format(xh3.getNumericCellValue());

                    Cell xh4 = hssfRow.getCell(3);//开始时间
                    Cell xh5 = hssfRow.getCell(4);//预约医生
                    Cell xh6 = hssfRow.getCell(5);//咨询项目
                    Cell xh7 = hssfRow.getCell(6); //到店状态
                    Cell xh8 = hssfRow.getCell(7);//创建时间
                    Cell xh9 = hssfRow.getCell(8);//
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    Cell xh14 = hssfRow.getCell(13);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2hs + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3hs + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null" ;
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14 + "") == null || ("").equals((xh14 + ""))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }

                    //客户表
                    String tableHead = ShiroKit.getUser().getAbbreviation();
                    String tableName = tableHead + "customer_info";

                    //客户ID
                    String id = CommUtils.getUUID();
                    Random rand = new Random();
                    int randomNumber = rand.nextInt(999999);
                    id = id + tableHead + randomNumber;

                    //预约ID
                    String reservationId = "";
                    Integer customerSex = 0;
                    String selectsql = "select * from  " + tableName + " where medical_record_number=" + xh2s + " limit 0,1;";
                    String ssql="select * from  " + tableName + " where customer_phone=" + xh3s + " limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    CustomerInfo customerInfo1 = sqlMapper.selectOne(ssql, CustomerInfo.class);
                    if (customerInfo != null) {
                        reservationId = customerInfo.getReservationsId();
                        customerSex = customerInfo.getCustomerSex();

                    }else if (customerInfo1 != null) {
                        reservationId = customerInfo1.getReservationsId();
                        customerSex = customerInfo1.getCustomerSex();

                    }else {
                        reservationId = "自到" + CommUtils.getUUID() + randomNumber;

                    }



                    //yellow_number
                    String sql1 = " select max(yellow_number) as yellowNumber from " + this.getTableName() + " ";
                    CustomerInfo customerInfo2 = sqlMapper.selectOne(sql1, CustomerInfo.class);
                    Integer yellowNumbers = 1;
                    if (customerInfo != null) {
                        yellowNumbers = Integer.parseInt(customerInfo2.getYellowNumber()) + 1;
                    }
                    String eTime = xh4s.replace("'", "").replace("/", "-");
                    String filingTime = xh8s.replace("'", "").replace("/", "-");
                    String eTimes = DateTimeKit.addDate(eTime, 1);

                    String sqladd = "INSERT INTO " + this.getTableName() + "(id,reservations_id,customer_name,medical_record_number,customer_phone," +
                            "start_time,doctor,appointment_pay_service,arrival_status," +
                            " archiving,filing_time,reservations,remarks,customer_status,field_tc,status,end_time,yellow_number,customer_sex) values " +
                            "('" + id + "','" + reservationId + "'," + xh1s + "," + xh2s + "," + xh3s + "," + xh4s + "," + xh5s + "," + xh6s + "," +
                            " " + xh7s + ", " + xh9s + ", '" + filingTime + "'," + xh10s + "," + xh11s + "," + xh12s + "," + xh13s + "," + xh14s + ",'" + eTimes + "','" + yellowNumbers + "','" + customerSex + "' );";
                    sqlMapper.insert(sqladd);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }


    /**
     * 导入
     */
    @RequestMapping(value = "/upload")
    public String importDealInfo(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    Cell xh14 = hssfRow.getCell(13);
                    Cell xh15 = hssfRow.getCell(14);
                    Cell xh16 = hssfRow.getCell(15);
                    Cell xh17 = hssfRow.getCell(16);
                    Cell xh18 = hssfRow.getCell(17);
                    Cell xh19 = hssfRow.getCell(18);
                    Cell xh20 = hssfRow.getCell(19);
                    Cell xh21 = hssfRow.getCell(20);
                    Cell xh22 = hssfRow.getCell(21);
                    Cell xh23 = hssfRow.getCell(22);
                    Cell xh24 = hssfRow.getCell(23);
                    Cell xh25 = hssfRow.getCell(24);
                    Cell xh26 = hssfRow.getCell(25);
                    Cell xh27 = hssfRow.getCell(26);
                    Cell xh28 = hssfRow.getCell(27);
                    Cell xh29 = hssfRow.getCell(28);
                    Cell xh30 = hssfRow.getCell(29);
                    Cell xh31 = hssfRow.getCell(30);
                    Cell xh32 = hssfRow.getCell(31);
                    Cell xh33 = hssfRow.getCell(32);
                    Cell xh34 = hssfRow.getCell(33);
                    Cell xh35 = hssfRow.getCell(34);
                    Cell xh36 = hssfRow.getCell(35);
                    Cell xh37 = hssfRow.getCell(36);
                    Cell xh38 = hssfRow.getCell(37);
                    Cell xh39 = hssfRow.getCell(38);
                    Cell xh40 = hssfRow.getCell(39);
                    Cell xh41 = hssfRow.getCell(40);
                    Cell xh42 = hssfRow.getCell(41);
                    Cell xh43 = hssfRow.getCell(42);
                    Cell xh44 = hssfRow.getCell(43);
                    Cell xh45 = hssfRow.getCell(44);
                    Cell xh46 = hssfRow.getCell(45);
                    Cell xh47 = hssfRow.getCell(46);
                    Cell xh48 = hssfRow.getCell(47);
                    Cell xh49 = hssfRow.getCell(48);
//                    Cell xh50 = hssfRow.getCell(49);
//                    Cell xh51 = hssfRow.getCell(50);
//                    Cell xh52 = hssfRow.getCell(51);
//                    Cell xh53 = hssfRow.getCell(52);
//                    Cell xh54 = hssfRow.getCell(53);

                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    String xh15s = "";
                    String xh16s = "";
                    String xh17s = "";
                    String xh18s = "";
                    String xh19s = "";
                    String xh20s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    String xh24s = "";
                    String xh25s = "";
                    String xh26s = "";
                    String xh27s = "";
                    String xh28s = "";
                    String xh29s = "";
                    String xh30s = "";
                    String xh31s = "";
                    String xh32s = "";
                    String xh33s = "";
                    String xh34s = "";
                    String xh35s = "";
                    String xh36s = "";
                    String xh37s = "";
                    String xh38s = "";
                    String xh39s = "";
                    String xh40s = "";
                    String xh41s = "";
                    String xh42s = "";
                    String xh43s = "";
                    String xh44s = "";
                    String xh45s = "";
                    String xh46s = "";
                    String xh47s = "";
                    String xh48s = "";
                    String xh49s = "";
//                    String xh50s = "";
//                    String xh51s = "";
//                    String xh52s = "";
//                    String xh53s = "";
//                    String xh54s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14 + "") == null || ("").equals((xh14 + ""))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    if ((xh15 + "") == null || ("").equals((xh15 + ""))) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh16 + "") == null || ("").equals((xh16 + ""))) {
                        xh16s = "null";
                    } else {
                        xh16s = "'" + xh16 + "'";
                    }
                    if ((xh17 + "") == null || ("").equals((xh17 + ""))) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh18 + "") == null || ("").equals((xh18 + ""))) {
                        xh18s = "null";
                    } else {
                        xh18s = "'" + xh18 + "'";
                    }
                    if ((xh19 + "") == null || ("").equals((xh19 + ""))) {
                        xh19s = "null";
                    } else {
                        xh19s = "'" + xh19 + "'";
                    }
                    if ((xh20 + "") == null || ("").equals((xh20 + ""))) {
                        xh20s = "null";
                    } else {
                        xh20s = "'" + xh20 + "'";
                    }
                    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }
                    if ((xh24 + "") == null || ("").equals((xh24 + ""))) {
                        xh24s = "null";
                    } else {
                        xh24s = "'" + xh24 + "'";
                    }
                    if ((xh25 + "") == null || ("").equals((xh25 + ""))) {
                        xh25s = "null";
                    } else {
                        xh25s = "'" + xh25 + "'";
                    }
                    if ((xh26 + "") == null || ("").equals((xh26 + ""))) {
                        xh26s = "null";
                    } else {
                        xh26s = "'" + xh26 + "'";
                    }
                    if ((xh27 + "") == null || ("").equals((xh27 + ""))) {
                        xh27s = "null";
                    } else {
                        xh27s = "'" + xh27 + "'";
                    }
                    if ((xh28 + "") == null || ("").equals((xh28 + ""))) {
                        xh28s = "null";
                    } else {
                        xh28s = "'" + xh28 + "'";
                    }
                    if ((xh29 + "") == null || ("").equals((xh29 + ""))) {
                        xh29s = "null";
                    } else {
                        xh29s = "'" + xh29 + "'";
                    }
                    if ((xh30 + "") == null || ("").equals((xh30 + ""))) {
                        xh30s = "null";
                    } else {
                        xh30s = "'" + xh30 + "'";
                    }
                    if ((xh31 + "") == null || ("").equals((xh31 + ""))) {
                        xh31s = "null";
                    } else {
                        xh31s = "'" + xh31 + "'";
                    }
                    if ((xh32 + "") == null || ("").equals((xh32 + ""))) {
                        xh32s = "null";
                    } else {
                        xh32s = "'" + xh32 + "'";
                    }
                    if ((xh33 + "") == null || ("").equals((xh33 + ""))) {
                        xh33s = "null";
                    } else {
                        xh33s = "'" + xh33 + "'";
                    }
                    if ((xh34 + "") == null || ("").equals((xh34 + ""))) {
                        xh34s = "null";
                    } else {
                        xh34s = "'" + xh34 + "'";
                    }
                    if ((xh35 + "") == null || ("").equals((xh35 + ""))) {
                        xh35s = "null";
                    } else {
                        xh35s = "'" + xh35 + "'";
                    }
                    if ((xh36 + "") == null || ("").equals((xh36 + ""))) {
                        xh36s = "null";
                    } else {
                        xh36s = "'" + xh36 + "'";
                    }
                    if ((xh37 + "") == null || ("").equals((xh37 + ""))) {
                        xh37s = "null";
                    } else {
                        xh37s = "'" + xh37 + "'";
                    }
                    if ((xh38 + "") == null || ("").equals((xh38 + ""))) {
                        xh38s = "null";
                    } else {
                        xh38s = "'" + xh38 + "'";
                    }
                    if ((xh39 + "") == null || ("").equals((xh39 + ""))) {
                        xh39s = "null";
                    } else {
                        xh39s = "'" + xh39 + "'";
                    }
                    if ((xh40 + "") == null || ("").equals((xh40 + ""))) {
                        xh40s = "null";
                    } else {
                        xh40s = "'" + xh40 + "'";
                    }
                    if ((xh41 + "") == null || ("").equals((xh41 + ""))) {
                        xh41s = "null";
                    } else {
                        xh41s = "'" + xh41 + "'";
                    }
                    if ((xh42 + "") == null || ("").equals((xh42 + ""))) {
                        xh42s = "null";
                    } else {
                        xh42s = "'" + xh42 + "'";
                    }
                    if ((xh43 + "") == null || ("").equals((xh43 + ""))) {
                        xh43s = "null";
                    } else {
                        xh43s = "'" + xh43 + "'";
                    }
                    if ((xh44 + "") == null || ("").equals((xh44 + ""))) {
                        xh44s = "null";
                    } else {
                        xh44s = "'" + xh44 + "'";
                    }
                    if ((xh45 + "") == null || ("").equals((xh45 + ""))) {
                        xh45s = "null";
                    } else {
                        xh45s = "'" + xh45 + "'";
                    }
                    if ((xh46 + "") == null || ("").equals((xh46 + ""))) {
                        xh46s = "null";
                    } else {
                        xh46s = "'" + xh46 + "'";
                    }
                    if ((xh47 + "") == null || ("").equals((xh47 + ""))) {
                        xh47s = "null";
                    } else {
                        xh47s = "'" + xh47 + "'";
                    }
                    if ((xh48 + "") == null || ("").equals((xh48 + ""))) {
                        xh48s = "null";
                    } else {
                        xh48s = "'" + xh48 + "'";
                    }
                    if ((xh49 + "") == null || ("").equals((xh49 + ""))) {
                        xh49s = "null";
                    } else {
                        xh49s = "'" + xh49 + "'";
                    }
//                    if ((xh50 + "") == null || ("").equals((xh50 + ""))) {
//                        xh50s = "null";
//                    } else {
//                        xh50s = "'" + xh50 + "'";
//                    }
//                    if ((xh51 + "") == null || ("").equals((xh51 + ""))) {
//                        xh51s = "null";
//                    } else {
//                        xh51s = "'" + xh51 + "'";
//                    }
//                    if ((xh52 + "") == null || ("").equals((xh52 + ""))) {
//                        xh52s = "null";
//                    } else {
//                        xh52s = "'" + xh52 + "'";
//                    }
//                    if ((xh53 + "") == null || ("").equals((xh53 + ""))) {
//                        xh53s = "null";
//                    } else {
//                        xh53s = "'" + xh53 + "'";
//                    }
//                    if ((xh54 + "") == null || ("").equals((xh54 + ""))) {
//                        xh54s = "null";
//                    } else {
//                        xh54s = "'" + xh54 + "'";
//                    }
                    String tableHead = ShiroKit.getUser().getAbbreviation();
                    String tableName1 = tableHead + "deal_info";
                    String tableName = tableHead + "recharge_info";
                    Random rands = new Random();
                    int randomNumber = rands.nextInt(999);
//                    String sql1 = "select * from " + tableName1;
//                    List<CustomerInfo> info = sqlMapper.selectList(sql1, CustomerInfo.class);
//                    int num = info.size() + 1;

                    String a1 = (xh6s.replace("'", "")).replace("-", "");
                    String a2 = a1.replace(" ", "");
                    String a3 = a2.replace(":", "");
                    String a4 = tableHead.replace("_", "");
                    String idd = "";
                    idd = a4 + randomNumber + a3;
                    //客户信息
                    CustomerInfo customerInfo = new CustomerInfo();
                    String id = "";
                    String reservationsId = "";
                    String selectsql = "select * from " + getTableName() + " where customer_phone=" + xh4s + " " +
                            " limit 0,1;";
                    customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if (customerInfo != null) {
                        id = customerInfo.getId();
                        reservationsId = customerInfo.getReservationsId();
                    } else {
                        String selectsql1 = "select * from " + getTableName() + " where customer_name=" + xh3s + " " +
                                " limit 0,1;";
                        customerInfo = sqlMapper.selectOne(selectsql1, CustomerInfo.class);
                        if (customerInfo != null) {
                            id = customerInfo.getId();
                            reservationsId = customerInfo.getReservationsId();
                        } else {
                            id = CommUtils.getUUID() + randomNumber;
                            reservationsId = CommUtils.getUUID();
                        }
                    }
                    String sqlup = "update " + getTableName() + " set medical_record_number=" + xh1s + " where reservations_id='" + reservationsId + "'";
                    sqlMapper.update(sqlup);
                    String payId = "";
                    //收费项目
                    String sqlPayService = "select * from pay_service_info where pay_service_name=" + xh11s + " limit 0,1;";
                    PayServiceInfo payService = sqlMapper.selectOne(sqlPayService, PayServiceInfo.class);
                    if (payService == null) {
                        String insertsql = "INSERT INTO `pay_service_info` (`num`,`department`, `first_level_project`, " +
                                "`pay_service_name`, `pay_service_price_one`, `pay_service_price_two`) VALUES" +
                                "(  " + 1 + " ,'e看牙','e看牙'" +
                                "," + xh11s + ",0," + xh13s + " );";
                        sqlMapper.insert(insertsql);
                        String sqlPayService1 = "select * from pay_service_info where pay_service_name=" + xh11s + " limit 0,1;";
                        PayServiceInfo payService1 = sqlMapper.selectOne(sqlPayService1, PayServiceInfo.class);
                        payId = payService1.getId();
                    } else {
                        payId = payService.getId();
                    }

                    //收费类型
                    if ("'收费'".equals(xh5s)) {
                        xh5s = "'5'";
                    } else if ("'退费'".equals(xh5s)) {
                        xh5s = "'4'";
                    } else if ("'还欠费'".equals(xh5s)) {
                        xh5s = "'3'";
                    } else if ("'部分退费'".equals(xh5s)) {
                        xh5s = "'1'";
                    } else if ("'作废'".equals(xh5s)) {
                        xh5s = "'6'";
                    } else {
                        xh5s = "'0'";
                    }

                    String sql = "insert into " + tableName1 + " " +
                            "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                            "service_actual_pay,folding_pay,pay_service,arrears,operation_time,get_time,remarks,nurse,doctor,id2 ) VALUES " +
                            "('" + idd + "' , " + xh5s + " , '" + id + "' , '斯美尔店' , '" + reservationsId + "', " +
                            " '" + payId + "', '已成交' , " + xh9s + "," + xh12s + " , " +
                            " " + xh15s + " , " + xh14s + " , " + xh13s + ", " + xh16s + " ," + xh6s + " ,  " +
                            " " + xh6s + " ," + xh17s + " , " + xh8s + " , " + xh7s + "," +
                            " '" + idd + "' );";

                    if (!"'0.0'".equals(xh18s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','现金'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh18s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh19s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','银行卡'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh19s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh20s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','微信支付'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh20s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh21s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','电子扫码'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh21s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                   /* if (!"'0.0'".equals(xh20s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','支付宝'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh20s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                    /*if (!"'0.0'".equals(xh21s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','代金劵'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh21s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    */
                    if (!"'0.0'".equals(xh22s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','美团点评'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh22s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh23s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','华荣时代'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh23s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh24s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','社保卡'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh24s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh25s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','天猫'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh25s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh26s)) {
                        idd = idd + randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','有钱花分期'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh26s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                   /* if (!"'0.0'".equals(xh24s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','充值代金券100抵1000'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh24s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh25s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','预付款'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh25s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh26s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','积分抵扣'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh26s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh27s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','么么分期'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh27s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                    /*if (!"'0.0'".equals(xh22s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','百度钱包'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh22s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh23s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','华夏刷卡'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh23s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh24s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','社保卡'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh24s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh25s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','荣华'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh25s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh26s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','积分抵扣'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh26s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                    /*if (!"'0.0'".equals(xh18s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','华银百汇扫码'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh18s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                    /*if (!"'0.0'".equals(xh21s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','银行卡'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh21s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh22s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','华夏刷卡'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh22s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                   /* if (!"'0.0'".equals(xh32s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','百度糯米'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh32s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                   /* if (!"'0.0'".equals(xh28s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','储值卡馈赠金'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh28s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }
                    if (!"'0.0'".equals(xh29s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','随行付'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh29s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                   /* if (!"'0.0'".equals(xh34s)) {
                        idd=idd+randomNumber;
                        String sqlinsert1 = "INSERT INTO " + tableName + " (`id`,`recharge_people`,`customer_id`,`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`recharge_time`,`operation_time`,remarks,document_number) VALUES" +
                                "('" + idd + "'," + xh10s + ",'" + id + "','储值卡本金'," + 2 + ",'" + reservationsId + "'," + xh10s + " " +
                                "," + xh34s + "," + xh6s + "," + xh6s + "," + xh17s + " ," + xh2s + ");";
                        sqlMapper.insert(sqlinsert1);
                    }*/
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }


    @RequestMapping(value = "/arrearInfo_import")
    public String arrearInfo_import(Model model) {
        return PREFIX + "arrearInfo_import.html";
    }


    /**
     * 导入
     */
    @RequestMapping(value = "/uploadArrears")
    public String uploadArrears(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);

                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";

                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }

                    //客户信息
                    /*String selectsql = "select * from " + getTableName() + " where customer_name=" + xh1s + " " +
                            " limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);*/

                    String sql = "update  " + this.getTableName() + "" +
                            " set actual_account_balance=" + xh3s + ",account_arrears=" + xh4s + " where customer_name= " + xh1s + "";
                    sqlMapper.update(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }


    @RequestMapping(value = "/medicalNumber_import")
    public String medicalNumber_import(Model model) {
        return PREFIX + "medicalNumber_import.html";
    }

    /**
     * 导入病历号
     */
    @RequestMapping(value = "/uploadMedicalNumber")
    public String uploadMedicalNumber(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);

                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";

                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    /*if (xh3.toString() != null) {
                        xh3s = xh3.toString();
                    } else {
                        xh3s = null;
                    }
*/
                 /*   //客户信息
                    String selectsql = "select * from " + getTableName() + " where customer_phone like '%" + xh3s + "%'" +
                            "  and customer_name= "+xh2s+" limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if(customerInfo!=null){
                        String sql = "update  " + this.getTableName() + "" +
                                " set medical_record_number=" + xh1s + " where customer_phone like '%" + xh3s + "%'  and customer_name= "+xh2s+"";
                        sqlMapper.update(sql);
                    }*/

                    //客户信息
                    String selectsql = "select * from " + getTableName() + " where   customer_name= " + xh2s + " limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if (customerInfo != null) {
                        String sql = "update  " + this.getTableName() + "" +
                                " set medical_record_number=" + xh1s + " where  customer_name= " + xh2s + "";
                        sqlMapper.update(sql);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }

    @RequestMapping(value = "/updateDocumentNumber", method = RequestMethod.POST)
    @ResponseBody
    public int updateDocumentNumber(String getTime, String oldDocumentNumber, String documentNumber, String customerId) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "recharge_info";
        String remark = ShiroKit.getUser().getAccount() + "在" + DateTimeKit.now() + "把单据号：" + oldDocumentNumber + "修改为：" + documentNumber;
        String sql = "update " + tableName + " set document_number='" + documentNumber + "'," +
                "remarkss='" + remark + "' where customer_id='" + customerId + "'  and operation_time='" + getTime + "'";
        return sqlMapper.update(sql);
    }

}
